package util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;

import com.gxuwz.Market.business.entity.ServiceData;
import com.gxuwz.Market.business.entity.ServiceForm;

public class ExcelUtil {
	
//	public static void createHeader(ServiceForm serviceForm, HSSFSheet sheet){
//		//创建行
//		HSSFRow row1 = sheet.createRow(0);
//		//创建单元格
//		HSSFCell cell = row1.createCell(0);
//		//设置单元格内容(标题)
//		cell.setCellValue(serviceForm.getFormName());
//		//合并单元格
//		sheet.addMergedRegion(new CellRangeAddress(0,0,0,serviceForm.getFieldNum()-1));
//		//分割表头
//		String[] fields = serviceForm.getFieldNames().split(",");
//		HSSFRow row2 = sheet.createRow(1);
//		int fieldSum = serviceForm.getFieldNum();
//		for(int i=0;i<fieldSum;i++) {
//			row2.createCell(i).setCellValue(fields[i]);
//		}
//	}
	

	
	public static HSSFWorkbook ListToExcel(ServiceForm format, List<ServiceData> dataList) throws IOException {
		//创建HSSFWorkbook对象(excel的文档对象)
	    HSSFWorkbook wb = new HSSFWorkbook();
	    //建立新的sheet对象（excel的表单）
	    HSSFSheet sheet=wb.createSheet(format.getFormName());
	    
	    HSSFCellStyle cellStyle = wb.createCellStyle();
	    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    
	    //创建表头
	    
    	//创建行
  		HSSFRow row1 = sheet.createRow(0);
  		//创建单元格
  		HSSFCell cell = row1.createCell(0);
  		//设置单元格内容(标题)
  		cell.setCellValue(format.getFormName());
  		cell.setCellStyle(cellStyle);
  		//合并单元格
  		sheet.addMergedRegion(new CellRangeAddress(0,0,0,format.getFieldNum()-1));
  		//分割表头
  		String[] fields = format.getFieldNames().split(",");
  		HSSFRow row2 = sheet.createRow(1);
  		int fieldSum = format.getFieldNum();
  		for(int i=0;i<fieldSum;i++) {
  			row2.createCell(i).setCellValue(fields[i]);
  		}
	  	//获得数据条数
	  	int dataSum = dataList.size();
	  	for(int i=2;i<dataSum+2;i++) {
	  		HSSFRow row = sheet.createRow(i);
	  		ServiceData data = dataList.get(i-2);
	  		String[] fieldset = data.getDatas();
	  		for(int j=0;j<fieldSum;j++) {
	  			row.createCell(j).setCellValue(fieldset[j]);
	  		}
	  	}
	  	
	  return wb;
	}
	
	/**
	 * 从Excel文件中读取文件格式
	 * @param file
	 * @return
	 */
	public static ServiceForm excelToForm(File file){
		ServiceForm form = new ServiceForm();
		try {
			//根据文件流转化成Excel表格
			InputStream is = new FileInputStream(file);
			HSSFWorkbook wb = new HSSFWorkbook(is);
			//读取工作表
			HSSFSheet sheet=wb.getSheetAt(0);
			HSSFRow row1 = sheet.getRow(0);
			HSSFCell cell1 = row1.getCell(0);
			//读取表格名
			form.setFormName(getCellFormatValue(cell1));
			
			HSSFRow row2 = sheet.getRow(1);
			//获取列数
			form.setFieldNum((int)row2.getLastCellNum());
			StringBuffer fNames = new StringBuffer();
			StringBuffer fTypes = new StringBuffer();
			StringBuffer fLens = new StringBuffer();
			for(int cellNum = 0;cellNum<row2.getLastCellNum();cellNum++){
				HSSFCell cell = row2.getCell(cellNum);
				//读取字段名
				fNames.append(getCellFormatValue(cell));
				fNames.append(",");
				fTypes.append("0,");
				fLens.append("20,");
			}
			form.setFieldNames(fNames.toString());
			form.setFieldType(fTypes.toString());
			form.setFieldLen(fLens.toString());		
			
		} catch (Exception e) {
			e.printStackTrace();
		}

		return form;
	}
	
	/**
	 * 从Excel中导入数据和格式
	 * @param file num数据项数 index唯一数据的下标
	 * @return
	 */
	public static List<ServiceData> excelToFormData(File file,int num,int index){
		List<ServiceData> dataList = new ArrayList<ServiceData>();
		try {
			//根据文件流转化成Excel表格
			InputStream is = new FileInputStream(file);
			HSSFWorkbook wb = new HSSFWorkbook(is);
			//读取工作表
			HSSFSheet sheet=wb.getSheetAt(0);
			//获得总行数
			int lastRow = sheet.getLastRowNum();
			for(int i=2;i<lastRow;i++){
				ServiceData data = new ServiceData();
				HSSFRow row = sheet.getRow(i);
				String datas[] = new String[num]; 
				for(int j=0;j<num;j++){
					HSSFCell cell = row.getCell(j);
					if(j == index){
						//读取填写者id
						data.setWriterId(getCellFormatValue(cell));
					}
					//读取数据
					datas[j] = getCellFormatValue(cell);
				}
				data.setDatas(datas);
				dataList.add(data);
			}
			
	
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	
		
		return dataList;
	}

	/**
	 * 获得唯一主键的下标
	 * @param serviceForm
	 * @param mainId
	 * @return
	 */
	public static int getIndex(ServiceForm serviceForm, String mainId){
		String[] names = serviceForm.getFieldNames().split(",");
		int i = 0;
		int index = -1;
		for(String name:names){
			if(mainId.equals(name)){
				index = i;
				break;
			}
			i++;
		}
		return index;
	}
	
	
	   private static String getCellFormatValue(HSSFCell cell) {
	        String cellvalue = "";
	       // DecimalFormat df = new DecimalFormat("#");
	        if (cell != null) {
	            // 判断当前Cell的Type
	            switch (cell.getCellType()) {
	            // 如果当前Cell的Type为NUMERIC
	            case HSSFCell.CELL_TYPE_NUMERIC: {
	                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式  
	                    SimpleDateFormat sdf = null;  
	                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat  
	                            .getBuiltinFormat("h:mm")) {  
	                        sdf = new SimpleDateFormat("HH:mm");  
	                    } else {// 日期  
	                        sdf = new SimpleDateFormat("yyyy-MM-dd");  
	                    }  
	                    Date date = cell.getDateCellValue();  
	                    cellvalue = sdf.format(date);  
	                } else if (cell.getCellStyle().getDataFormat() == 31) {  
	                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
	                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
	                    double value = cell.getNumericCellValue();  
	                    Date date = org.apache.poi.ss.usermodel.DateUtil  
	                            .getJavaDate(value);  
	                    cellvalue = sdf.format(date);  
	                } else {  
	                    double value = cell.getNumericCellValue();  
	                    CellStyle style = cell.getCellStyle();  
	                    DecimalFormat format = new DecimalFormat();  
	                    String temp = style.getDataFormatString();  
	                    // 单元格设置成常规  
	                    if (temp.equals("General")) {  
	                        format.applyPattern("#.######");  
	                    }  
	                    cellvalue = format.format(value);  
	                }  
	                break;  
	            }
	            case HSSFCell.CELL_TYPE_FORMULA: {
	                // 判断当前的cell是否为Date
	                if (HSSFDateUtil.isCellDateFormatted(cell)) {
	                    // 如果是Date类型则，转化为Data格式
	 
	                    //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
	                    //cellvalue = cell.getDateCellValue().toLocaleString();
	 
	                    //方法2：这样子的data格式是不带带时分秒的：2011-10-12
	                    Date date = cell.getDateCellValue();
	                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	                    cellvalue = sdf.format(date);
	 
	                }
	                // 如果是纯数字
	                else {
	                    // 取得当前Cell的数值
	                    cellvalue = new DecimalFormat("#.######").format(cell.getNumericCellValue());  
	                }
	                break;
	            }
	            // 如果当前Cell的Type为STRIN
	            case HSSFCell.CELL_TYPE_STRING:
	                // 取得当前的Cell字符串
	                cellvalue = cell.getRichStringCellValue().getString();
	                break;
	            // 默认的Cell值
	            default:
	                cellvalue = "";
	            }
	        } else {
	            cellvalue = "";
	        }
	        return cellvalue;
	    }
	
}
